Skip Headers

Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 28 Rule-Based Application Example

This chapter illustrates a rule-based application that uses the Oracle rules engine.

The examples in this chapter are independent of Streams. That is, no Streams capture processes, propagations, apply processes, or messaging clients are clients of the rules engine in these examples, and no queues are used.

This chapter contains these topics:

- Overview of the Rule-Based Application

- Using Rules on Nontable Data Stored in Explicit Variables

- Using Rules on Data in Explicit Variables with Iterative Results

- Using Partial Evaluation of Rules on Data in Explicit Variables

- Using Rules on Data Stored in a Table

- Using Rules on Both Explicit Variables and Table Data

- Using Rules on Implicit Variables and Table Data

- Using Event Contexts and Implicit Variables with Rules

- Dispatching Problems and Checking Results for the Table Examples

See Also: Chapter 5, "Rules" Chapter 14, "Managing Rules" Chapter 23, "Monitoring Rules"

## Overview of the Rule-Based Application

Each example in this chapter creates a rule-based application that handles customer problems. The application uses rules to determine actions that must be completed based on the problem priority when a new problem is reported. For example, the application assigns each problem to a particular company center based on the problem priority.

The application enforces these rules using the rules engine. An evaluation context named `evalctx` is created to define the information surrounding a support problem. Rules are created based on the requirements described previously, and they are added to a rule set named `rs`.

The task of assigning problems is done by a user-defined procedure named `problem_dispatch`, which calls the rules engine to evaluate rules in the rule set `rs` and then takes appropriate action based on the rules that evaluate to `TRUE`.

## Using Rules on Nontable Data Stored in Explicit Variables

This example illustrates how to use rules to evaluate data stored in explicit variables. This example handles customer problems based on priority and uses the following rules for handling customer problems:

- Assign all problems with priority greater than 2 to the San Jose Center.

- Assign all problems with priority less than or equal to 2 to the New York Center.

- Send an alert to the vice president of support for a problem with priority equal to 1.

The evaluation context contains only one explicit variable named `priority`, which refers to the priority of the problem being dispatched. The value for this variable is passed to `DBMS_RULE.EVALUATE` procedure by the `problem_dispatch` procedure.

Complete the following steps:

1. Show Output and Spool Results

2. Create the support User

3. Grant the support User the Necessary System Privileges on Rules

4. Create the evalctx Evaluation Context

5. Create the Rules that Correspond to Problem Priority

6. Create the rs Rule Set

7. Add the Rules to the Rule Set

8. Query the Data Dictionary

9. Create the problem_dispatch PL/SQL Procedure

10. Dispatch Sample Problems

11. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

    /************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run `SET` `ECHO` `ON` and specify the spool file for the script. Check the spool file for errors after you run this script.

    */

    SET ECHO ON
    SPOOL rules_stored_variables.out

    /*

Step 2 Create the support User

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
      CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE,
      CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE
    TO support IDENTIFIED BY support;

    /*

Step 3 Grant the support User the Necessary System Privileges on Rules

    */

    BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
        grantee      => 'support',
        grant_option => false);
    END;
    /

    /*

Step 4 Create the evalctx Evaluation Context

    */

    CONNECT support/support

    SET FEEDBACK 1
    SET NUMWIDTH 10
    SET LINESIZE 80
    SET TRIMSPOOL ON
    SET TAB OFF
    SET PAGESIZE 100
    SET SERVEROUTPUT ON
    DECLARE
      vt SYS.RE$VARIABLE_TYPE_LIST;
    BEGIN
      vt := SYS.RE$VARIABLE_TYPE_LIST(
        SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL));
      DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
        evaluation_context_name    => 'evalctx',
        variable_types             => vt,
        evaluation_context_comment => 'support problem definition');
    END;
    /

    /*

Step 5 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

    */

    DECLARE
      ac  SYS.RE$NV_LIST;
    BEGIN
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('San Jose'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r1',
        condition      => ':priority > 2',
        action_context => ac,
        rule_comment   => 'Low priority problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('New York'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r2',
        condition      => ':priority <= 2',
        action_context => ac,
        rule_comment   => 'High priority problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('ALERT', ANYDATA.CONVERTVARCHAR2('John Doe'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r3',
        condition      => ':priority = 1',
        action_context => ac,
        rule_comment   => 'Urgent problems');
    END;
    /

    /*

Step 6 Create the rs Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.CREATE_RULE_SET(
        rule_set_name      => 'rs',
        evaluation_context => 'evalctx',
        rule_set_comment   => 'support rules');
    END;
    /

    /*

Step 7 Add the Rules to the Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r1',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r2',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r3',
        rule_set_name => 'rs');
    END;
    /

    /*

Step 8 Query the Data Dictionary

At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.

    */

    COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
    COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40

    SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT
      FROM USER_EVALUATION_CONTEXTS
      ORDER BY EVALUATION_CONTEXT_NAME;

    SET LONGCHUNKSIZE 4000
    SET LONG 4000
    COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5
    COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35
    COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10
    COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10

    SELECT RULE_NAME,
           RULE_CONDITION,
           AC.NVN_NAME ACTION_CONTEXT_NAME,
           AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
      FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
      ORDER BY RULE_NAME;

    COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
    COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
    COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25
    COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15

    SELECT RULE_SET_NAME,
           RULE_SET_EVAL_CONTEXT_OWNER,
           RULE_SET_EVAL_CONTEXT_NAME,
           RULE_SET_COMMENT
      FROM USER_RULE_SETS
      ORDER BY RULE_SET_NAME;

    /*

Step 9 Create the problem_dispatch PL/SQL Procedure

    */

    CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER)
    IS
        vv        SYS.RE$VARIABLE_VALUE;
        vvl       SYS.RE$VARIABLE_VALUE_LIST;
        truehits  SYS.RE$RULE_HIT_LIST;
        maybehits SYS.RE$RULE_HIT_LIST;
        ac        SYS.RE$NV_LIST;
        namearray SYS.RE$NAME_ARRAY;
        name      VARCHAR2(30);
        cval      VARCHAR2(100);
        rnum      INTEGER;
        i         INTEGER;
        status    PLS_INTEGER;
    BEGIN
      vv  := SYS.RE$VARIABLE_VALUE('priority',
                                   ANYDATA.CONVERTNUMBER(priority));
      vvl := SYS.RE$VARIABLE_VALUE_LIST(vv);
      truehits := SYS.RE$RULE_HIT_LIST();
      maybehits := SYS.RE$RULE_HIT_LIST();
      DBMS_RULE.EVALUATE(
          rule_set_name        => 'support.rs',
          evaluation_context   => 'evalctx',
          variable_values      => vvl,
          true_rules           => truehits,
          maybe_rules          => maybehits);
      FOR rnum IN 1..truehits.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
        ac := truehits(rnum).rule_action_context;
        namearray := ac.GET_ALL_NAMES;
          FOR i IN 1..namearray.count loop
            name := namearray(i);
            status := ac.GET_VALUE(name).GETVARCHAR2(cval);
            IF (name = 'CENTER') then
              DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
            ELSIF (name = 'ALERT') THEN
              DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval);
            END IF;
          END LOOP;
      END LOOP;
    END;
    /

    /*

Step 10 Dispatch Sample Problems

    */

    EXECUTE problem_dispatch(1);
    EXECUTE problem_dispatch(2);
    EXECUTE problem_dispatch(3);
    EXECUTE problem_dispatch(5);

    /*

Step 11 Check the Spool Results

Check the `rules_stored_variables.out` spool file to ensure that all actions completed successfully after this script completes.

    */

    SET ECHO OFF
    SPOOL OFF

    /*************************** END OF SCRIPT ******************************/

## Using Rules on Data in Explicit Variables with Iterative Results

This example is the same as the previous example "Using Rules on Nontable Data Stored in Explicit Variables", except that this example returns evaluation results iteratively instead of all at once.

Complete the following steps:

1. Show Output and Spool Results

2. Make Sure You Have Completed the Preliminary Steps

3. Replace the problem_dispatch PL/SQL Procedure

4. Dispatch Sample Problems

5. Clean Up the Environment (Optional)

6. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

    /************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run `SET` `ECHO` `ON` and specify the spool file for the script. Check the spool file for errors after you run this script.

    */

    SET ECHO ON
    SPOOL rules_stored_variables_iterative.out

    /*

Step 2 Make Sure You Have Completed the Preliminary Steps

Make sure you have completed Steps 1 to 8 in the "Using Rules on Nontable Data Stored in Explicit Variables". If you have not completed these steps, then complete them before you continue.

    */

    PAUSE Press <RETURN> to continue when the preliminary steps have been completed.

    /*

Step 3 Replace the problem_dispatch PL/SQL Procedure

Replace the `problem_dispatch` procedure created in Step 9 with the procedure in this step. The difference between the two procedures is that the procedure created in Step 9 returns all evaluation results at once while the procedure in this step returns evaluation results iteratively.

    */

    CONNECT support/support

    SET SERVEROUTPUT ON
    CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER)
    IS
        vv          SYS.RE$VARIABLE_VALUE;
        vvl         SYS.RE$VARIABLE_VALUE_LIST;
        truehits    BINARY_INTEGER;
        maybehits   BINARY_INTEGER;
        hit         SYS.RE$RULE_HIT;
        ac          SYS.RE$NV_LIST;
        namearray   SYS.RE$NAME_ARRAY;
        name        VARCHAR2(30);
        cval        VARCHAR2(100);
        i           INTEGER;
        status      PLS_INTEGER;
        iter_closed EXCEPTION;
        pragma exception_init(iter_closed, -25453);
    BEGIN
      vv  := SYS.RE$VARIABLE_VALUE('priority',
                                   ANYDATA.CONVERTNUMBER(priority));
      vvl := SYS.RE$VARIABLE_VALUE_LIST(vv);
      DBMS_RULE.EVALUATE(
          rule_set_name        => 'support.rs',
          evaluation_context   => 'evalctx',
          variable_values      => vvl,
          true_rules_iterator  => truehits,
          maybe_rules_iterator => maybehits);
      LOOP
        hit := DBMS_RULE.GET_NEXT_HIT(truehits);
        EXIT WHEN hit IS NULL;
        DBMS_OUTPUT.PUT_LINE('Using rule '|| hit.rule_name);
        ac := hit.rule_action_context;
        namearray := ac.GET_ALL_NAMES;
          FOR i IN 1..namearray.COUNT LOOP
            name := namearray(i);
            status := ac.GET_VALUE(name).GETVARCHAR2(cval);
            IF (name = 'CENTER') then
              DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
            ELSIF (name = 'ALERT') THEN
              DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval);
            END IF;
          END LOOP;
      END LOOP;
      -- Close iterators
      BEGIN
        DBMS_RULE.CLOSE_ITERATOR(truehits);
      EXCEPTION
        WHEN iter_closed THEN
          NULL;
      END;
      BEGIN
        DBMS_RULE.CLOSE_ITERATOR(maybehits);
      EXCEPTION
        WHEN iter_closed THEN
          NULL;
      END;
    END;
    /

    /*

Step 4 Dispatch Sample Problems

    */

    EXECUTE problem_dispatch(1);
    EXECUTE problem_dispatch(2);
    EXECUTE problem_dispatch(3);
    EXECUTE problem_dispatch(5);

    /*

Step 5 Clean Up the Environment (Optional)

You can clean up the sample environment by dropping the `support` user.

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    DROP USER support CASCADE;

    /*

Step 6 Check the Spool Results

Check the `rules_stored_variables_iterative.out` spool file to ensure that all actions completed successfully after this script completes.

    */

    SET ECHO OFF
    SPOOL OFF

    /*************************** END OF SCRIPT ******************************/

## Using Partial Evaluation of Rules on Data in Explicit Variables

This example illustrates how to use partial evaluation when an event causes rules to evaluate to `MAYBE` instead of `TRUE` or `FALSE`. This example handles customer problems based on priority and problem type, and uses the following rules for handling customer problems:

- Assign all problems whose problem type is `HARDWARE` to the San Jose Center.

- Assign all problems whose problem type is `SOFTWARE` to the New York Center.

- Assign all problems whose problem type is `NULL` (unknown) to the Texas Center.

- Send an alert to the vice president of support for a problem with priority equal to 1.

Problems whose problem type is `NULL` evaluate to `MAYBE`. This example uses partial evaluation to take an action when `MAYBE` rules are returned to the rules engine client. In this case, the action is to assign the problem to the Texas Center.

The evaluation context contains an explicit variable named `priority`, which refers to the priority of the problem being dispatched. The evaluation context also contains an explicit variable named `problem_type`, which refers to the type of problem being dispatched (either `HARDWARE` or `SOFTWARE`). The values for these variables are passed to `DBMS_RULE.EVALUATE` procedure by the `problem_dispatch` procedure.

Complete the following steps:

1. Show Output and Spool Results

2. Create the support User

3. Grant the support User the Necessary System Privileges on Rules

4. Create the evalctx Evaluation Context

5. Create the Rules that Correspond to Problem Priority

6. Create the rs Rule Set

7. Add the Rules to the Rule Set

8. Query the Data Dictionary

9. Create the problem_dispatch PL/SQL Procedure

10. Dispatch Sample Problems

11. Clean Up the Environment (Optional)

12. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

    /************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run `SET` `ECHO` `ON` and specify the spool file for the script. Check the spool file for errors after you run this script.

    */

    SET ECHO ON
    SPOOL rules_stored_variables_partial.out

    /*

Step 2 Create the support User

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
      CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE,
      CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE
    TO support IDENTIFIED BY support;

    /*

Step 3 Grant the support User the Necessary System Privileges on Rules

    */

    BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
        grantee      => 'support',
        grant_option => false);
    END;
    /

    /*

Step 4 Create the evalctx Evaluation Context

    */

    CONNECT support/support

    SET FEEDBACK 1
    SET NUMWIDTH 10
    SET LINESIZE 80
    SET TRIMSPOOL ON
    SET TAB OFF
    SET PAGESIZE 100
    SET SERVEROUTPUT ON
    DECLARE
      vt  SYS.RE$VARIABLE_TYPE_LIST;
    BEGIN
      vt := SYS.RE$VARIABLE_TYPE_LIST(
            SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL),
            SYS.RE$VARIABLE_TYPE('problem_type', 'VARCHAR2(30)', NULL, NULL));
      DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
        evaluation_context_name    => 'evalctx',
        variable_types             => vt,
        evaluation_context_comment => 'support problem definition');
    end;
    /

    /*

Step 5 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

    */

    DECLARE
      ac  SYS.RE$NV_LIST;
    begin
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('ALERT', ANYDATA.CONVERTVARCHAR2('John Doe'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r1',
        condition      => ':priority = 1',
        action_context => ac,
        rule_comment   => 'Urgent problems');
      ac := sys.re$nv_list(NULL);
      ac.ADD_PAIR('TRUE CENTER', ANYDATA.CONVERTVARCHAR2('San Jose'));
      ac.ADD_PAIR('MAYBE CENTER', ANYDATA.CONVERTVARCHAR2('Texas'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name       => 'r2',
        condition       => ':problem_type = ''HARDWARE''',
        action_context  => ac,
        rule_comment    => 'Hardware problems');
      ac := sys.re$nv_list(NULL);
      ac.ADD_PAIR('TRUE CENTER', ANYDATA.CONVERTVARCHAR2('New York'));
      ac.ADD_PAIR('MAYBE CENTER', ANYDATA.CONVERTVARCHAR2('Texas'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name       => 'r3',
        condition       => ':problem_type = ''SOFTWARE''',
        action_context  => ac,
        rule_comment    => 'Software problems');
    END;
    /

    /*

Step 6 Create the rs Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.CREATE_RULE_SET(
        rule_set_name      => 'rs',
        evaluation_context => 'evalctx',
        rule_set_comment   => 'support rules');
    END;
    /

    /*

Step 7 Add the Rules to the Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r1',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r2',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r3',
        rule_set_name => 'rs');
    END;
    /

    /*

Step 8 Query the Data Dictionary

At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.

    */

    COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
    COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40

    SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT
      FROM USER_EVALUATION_CONTEXTS
      ORDER BY EVALUATION_CONTEXT_NAME;

    SET LONGCHUNKSIZE 4000
    SET LONG 4000
    COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5
    COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35
    COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10
    COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10

    SELECT RULE_NAME,
           RULE_CONDITION,
           AC.NVN_NAME ACTION_CONTEXT_NAME,
           AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
      FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
      ORDER BY RULE_NAME;

    COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
    COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
    COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25
    COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15

    SELECT RULE_SET_NAME,
           RULE_SET_EVAL_CONTEXT_OWNER,
           RULE_SET_EVAL_CONTEXT_NAME,
           RULE_SET_COMMENT
      FROM USER_RULE_SETS
      ORDER BY RULE_SET_NAME;

    /*

Step 9 Create the problem_dispatch PL/SQL Procedure

    */

    CREATE OR REPLACE PROCEDURE problem_dispatch (priority     NUMBER,
                                                  problem_type VARCHAR2 := NULL)
    IS
        vvl       SYS.RE$VARIABLE_VALUE_LIST;
        truehits  SYS.RE$RULE_HIT_LIST;
        maybehits SYS.RE$RULE_HIT_LIST;
        ac        SYS.RE$NV_LIST;
        namearray SYS.RE$NAME_ARRAY;
        name      VARCHAR2(30);
        cval      VARCHAR2(100);
        rnum      INTEGER;
        i         INTEGER;
        status    PLS_INTEGER;
    BEGIN
      IF (problem_type IS NULL) THEN
        vvl  := SYS.RE$VARIABLE_VALUE_LIST(
                SYS.RE$VARIABLE_VALUE('priority',
                                      ANYDATA.CONVERTNUMBER(priority)));
      ELSE
        vvl  := SYS.RE$VARIABLE_VALUE_LIST(
                SYS.RE$VARIABLE_VALUE('priority',
                                      ANYDATA.CONVERTNUMBER(priority)),
                SYS.RE$VARIABLE_VALUE('problem_type',
                                      ANYDATA.CONVERTVARCHAR2(problem_type)));
      END IF;
      truehits := SYS.RE$RULE_HIT_LIST();
      maybehits := SYS.RE$RULE_HIT_LIST();
      DBMS_RULE.EVALUATE(
          rule_set_name        => 'support.rs',
          evaluation_context   => 'evalctx',
          variable_values      => vvl,
          true_rules           => truehits,
          maybe_rules          => maybehits);
      FOR rnum IN 1..truehits.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
        ac := truehits(rnum).rule_action_context;
        namearray := ac.GET_ALL_NAMES;
          FOR i IN 1..namearray.count LOOP
            name := namearray(i);
            status := ac.GET_VALUE(name).GETVARCHAR2(cval);
            IF (name = 'TRUE CENTER') then
              DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
            ELSIF (name = 'ALERT') THEN
              DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval);
            END IF;
          END LOOP;
      END LOOP;
      FOR rnum IN 1..maybehits.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Using rule '|| maybehits(rnum).rule_name);
        ac := maybehits(rnum).rule_action_context;
        namearray := ac.GET_ALL_NAMES;
          FOR i IN 1..namearray.count loop
            name := namearray(i);
            status := ac.GET_VALUE(name).GETVARCHAR2(cval);
            IF (name = 'MAYBE CENTER') then
              DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
            END IF;
          END LOOP;
      END LOOP;
    END;
    /

    /*

Step 10 Dispatch Sample Problems

The first problem dispatch in this step uses partial evaluation and takes an action based on the partial evaluation. Specifically, the first problem dispatch specifies that the `priority` is `1` and the `problem_type` is `NULL`. In this case, the rules engine returns a `MAYBE` rule for the event, and the `problem_dispatch` procedure assigns the problem to the Texas center.

The second and third problem dispatches do not use partial evaluation. Each of these problems evaluate to `TRUE` for a rule, and the problem is assigned accordingly by the `problem_dispatch` procedure.

    */

    EXECUTE problem_dispatch(1, NULL);
    EXECUTE problem_dispatch(2, 'HARDWARE');
    EXECUTE problem_dispatch(3, 'SOFTWARE');

    /*

Step 11 Clean Up the Environment (Optional)

You can clean up the sample environment by dropping the `support` user.

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    DROP USER support CASCADE;

    /*

Step 12 Check the Spool Results

Check the `rules_stored_variables_partial.out` spool file to ensure that all actions completed successfully after this script completes.

    */

    SET ECHO OFF
    SPOOL OFF

    /*************************** END OF SCRIPT ******************************/

## Using Rules on Data Stored in a Table

This example illustrates how to use rules to evaluate data stored in a table. This example is similar to the example described in "Using Rules on Nontable Data Stored in Explicit Variables". In both examples, the application routes customer problems based on priority. However, in this example, the problems are stored in a table instead of variables.

The application uses the `problems` table in the `support` schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:

- Assign all problems with priority greater than 2 to the San Jose Center.

- Assign all problems with priority less than or equal to 2 to the New York Center.

- Send an alert to the vice president of support for a problem with priority equal to 1.

The evaluation context consists of the `problems` table. The relevant row of the table, which corresponds to the problem being routed, is passed to the `DBMS_RULE.EVALUATE` procedure as a table value.

Complete the following steps:

1. Show Output and Spool Results

2. Create the support User

3. Grant the support User the Necessary System Privileges on Rules

4. Create the problems Table

5. Create the evalctx Evaluation Context

6. Create the Rules that Correspond to Problem Priority

7. Create the rs Rule Set

8. Add the Rules to the Rule Set

9. Create the problem_dispatch PL/SQL Procedure

10. Log Problems

11. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

    /************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run `SET` `ECHO` `ON` and specify the spool file for the script. Check the spool file for errors after you run this script.

    */

    SET ECHO ON
    SPOOL rules_table.out

    /*

Step 2 Create the support User

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    CREATE TABLESPACE support_tbs1 DATAFILE 'support_tbs1.dbf'   SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    CREATE USER support
    IDENTIFIED BY support
      DEFAULT TABLESPACE support_tbs1
      QUOTA UNLIMITED ON support_tbs1;

    GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
      CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE,
      CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE
    TO support;

    /*

Step 3 Grant the support User the Necessary System Privileges on Rules

    */

    BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
        grantee      => 'support',
        grant_option => false);
    END;
    /

    /*

Step 4 Create the problems Table

    */

    CONNECT support/support

    SET FEEDBACK 1
    SET NUMWIDTH 10
    SET LINESIZE 80
    SET TRIMSPOOL ON
    SET TAB OFF
    SET PAGESIZE 100
    SET SERVEROUTPUT ON

    CREATE TABLE problems(
      probid          NUMBER PRIMARY KEY,
      custid          NUMBER,
      priority        NUMBER,
      description     VARCHAR2(4000),
      center          VARCHAR2(100));

    /*

Step 5 Create the evalctx Evaluation Context

    */

    DECLARE
      ta  SYS.RE$TABLE_ALIAS_LIST;
    BEGIN
      ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems'));
      DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
        evaluation_context_name    => 'evalctx',
        table_aliases              => ta,
        evaluation_context_comment => 'support problem definition');
    END;
    /

    /*

Step 6 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

    */

    DECLARE
      ac  SYS.RE$NV_LIST;
    BEGIN
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('San Jose'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r1',
        condition      => 'prob.priority > 2',
        action_context => ac,
        rule_comment   => 'Low priority problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('New York'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r2',
        condition      => 'prob.priority <= 2',
        action_context => ac,
        rule_comment   => 'High priority problems');
      ac := sys.RE$NV_LIST(NULL);
      ac.ADD_PAIR('ALERT', ANYDATA.CONVERTVARCHAR2('John Doe'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r3',
        condition      => 'prob.priority = 1',
        action_context => ac,
        rule_comment   => 'Urgent problems');
    END;
    /

    /*

Step 7 Create the rs Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.CREATE_RULE_SET(
        rule_set_name      => 'rs',
        evaluation_context => 'evalctx',
        rule_set_comment   => 'support rules');
    END;
    /

    /*

Step 8 Add the Rules to the Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r1',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r2',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r3',
        rule_set_name => 'rs');
    END;
    /

    /*

Step 9 Create the problem_dispatch PL/SQL Procedure

    */

    CREATE OR REPLACE PROCEDURE problem_dispatch
    IS
        cursor c IS SELECT probid, rowid FROM problems WHERE center IS NULL;
        tv        SYS.RE$TABLE_VALUE;
        tvl       SYS.RE$TABLE_VALUE_LIST;
        truehits  SYS.RE$RULE_HIT_LIST;
        maybehits SYS.RE$RULE_HIT_LIST;
        ac        SYS.RE$NV_LIST;
        namearray SYS.RE$NAME_ARRAY;
        name      VARCHAR2(30);
        cval      VARCHAR2(100);
        rnum      INTEGER;
        i         INTEGER;
        status    PLS_INTEGER;
    BEGIN
      FOR r IN c LOOP
        tv  := SYS.RE$TABLE_VALUE('prob', rowidtochar(r.rowid));
        tvl := SYS.RE$TABLE_VALUE_LIST(tv);
        truehits := SYS.RE$RULE_HIT_LIST();
        maybehits := SYS.RE$RULE_HIT_LIST();
        DBMS_RULE.EVALUATE(
          rule_set_name        => 'support.rs',
          evaluation_context   => 'evalctx',
          table_values         => tvl,
          true_rules           => truehits,
          maybe_rules          => maybehits);
        FOR rnum IN 1..truehits.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
          ac := truehits(rnum).rule_action_context;
          namearray := ac.GET_ALL_NAMES;
          FOR i IN 1..namearray.COUNT LOOP
            name := namearray(i);
            status := ac.GET_VALUE(name).GETVARCHAR2(cval);
            IF (name = 'CENTER') THEN
              UPDATE PROBLEMS SET center = cval WHERE rowid = r.rowid;
              DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval);
            ELSIF (name = 'ALERT') THEN
              DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid);
            END IF;
           END LOOP;
        END LOOP;
      END LOOP;
    END;
    /

    /*

Step 10 Log Problems

    */

    INSERT INTO problems(probid, custid, priority, description)
      VALUES(10101, 11, 1, 'no dial tone');

    INSERT INTO problems(probid, custid, priority, description)
      VALUES(10102, 21, 2, 'noise on local calls');

    INSERT INTO problems(probid, custid, priority, description)
      VALUES(10103, 31, 3, 'noise on long distance calls');

    COMMIT;

    /*

Step 11 Check the Spool Results

Check the `rules_table.out` spool file to ensure that all actions completed successfully after this script completes.

    */

    SET ECHO OFF
    SPOOL OFF

    /*************************** END OF SCRIPT ******************************/

See Also: "Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem dispatch

## Using Rules on Both Explicit Variables and Table Data

This example illustrates how to use rules to evaluate data stored in explicit variables and in a table. The application uses the `problems` table in the `support` schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:

- Assign all problems with priority greater than 2 to the San Jose Center.

- Assign all problems with priority equal to 2 to the New York Center.

- Assign all problems with priority equal to 1 to the Tampa Center from 8 AM to 8 PM.

- Assign all problems with priority equal to 1 to the Bangalore Center from 8 PM to 8 AM.

- Send an alert to the vice president of support for a problem with priority equal to 1.

The evaluation context consists of the `problems` table. The relevant row of the table, which corresponds to the problem being routed, is passed to the `DBMS_RULE.EVALUATE` procedure as a table value.

Some of the rules in this example refer to the current time, which is represented as an explicit variable named `current_time`. The current time is treated as additional data in the evaluation context. It is represented as a variable for the following reasons:

- It is not practical to store the current time in a table because it would have to be updated very often.

- The current time can be accessed by inserting calls to `SYSDATE` in every rule that requires it, but that would cause repeated invocations of the same SQL function `SYSDATE`, which might slow down rule evaluation. Different values of the current time in different rules might lead to incorrect behavior.

Complete the following steps:

1. Show Output and Spool Results

2. Create the support User

3. Grant the support User the Necessary System Privileges on Rules

4. Create the problems Table

5. Create the evalctx Evaluation Context

6. Create the Rules that Correspond to Problem Priority

7. Create the rs Rule Set

8. Add the Rules to the Rule Set

9. Create the problem_dispatch PL/SQL Procedure

10. Log Problems

11. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

    /************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run `SET` `ECHO` `ON` and specify the spool file for the script. Check the spool file for errors after you run this script.

    */

    SET ECHO ON
    SPOOL rules_var_tab.out

    /*

Step 2 Create the support User

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    CREATE TABLESPACE support_tbs2 DATAFILE 'support_tbs2.dbf'   SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    CREATE USER support
    IDENTIFIED BY support
      DEFAULT TABLESPACE support_tbs2
      QUOTA UNLIMITED ON support_tbs2;

    GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
      CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE,
      CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE
    TO support;

    /*

Step 3 Grant the support User the Necessary System Privileges on Rules

    */

    BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
        grantee      => 'support',
        grant_option => false);
    END;
    /

    /*

Step 4 Create the problems Table

    */

    CONNECT support/support

    SET FEEDBACK 1
    SET NUMWIDTH 10
    SET LINESIZE 80
    SET TRIMSPOOL ON
    SET TAB OFF
    SET PAGESIZE 100
    SET SERVEROUTPUT ON

    CREATE TABLE problems(
      probid          NUMBER PRIMARY KEY,
      custid          NUMBER,
      priority        NUMBER,
      description     VARCHAR2(4000),
      center          VARCHAR2(100));

    /*

Step 5 Create the evalctx Evaluation Context

    */

    DECLARE
      ta SYS.RE$TABLE_ALIAS_LIST;
      vt SYS.RE$VARIABLE_TYPE_LIST;
    BEGIN
      ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems'));
      vt := SYS.RE$VARIABLE_TYPE_LIST(
              SYS.RE$VARIABLE_TYPE('current_time', 'DATE', NULL, NULL));
      DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
        evaluation_context_name    => 'evalctx',
        table_aliases              => ta,
        variable_types             => vt,
        evaluation_context_comment => 'support problem definition');
    END;
    /

    /*

Step 6 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

    */

    DECLARE
      ac SYS.RE$NV_LIST;
    BEGIN
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('San Jose'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r1',
        condition      => 'prob.priority > 2',
        action_context => ac,
        rule_comment   => 'Low priority problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('New York'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r2',
        condition      => 'prob.priority = 2',
        action_context => ac,
        rule_comment   => 'High priority problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('ALERT', ANYDATA.CONVERTVARCHAR2('John Doe'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r3',
        condition      => 'prob.priority = 1',
        action_context => ac,
        rule_comment   => 'Urgent problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('Tampa'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name => 'r4',
        condition => '(prob.priority = 1) and ' ||
                     '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) >= 8) and ' ||
                     '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) <= 20)',
        action_context => ac,
        rule_comment => 'Urgent daytime problems');
      ac := sys.RE$NV_LIST(NULL);
      ac.add_pair('CENTER', ANYDATA.CONVERTVARCHAR2('Bangalore'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name => 'r5',
        condition => '(prob.priority = 1) and ' ||
                     '((TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) < 8) or ' ||
                     ' (TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) > 20))',
        action_context => ac,
        rule_comment => 'Urgent nighttime problems');
    END;
    /

    /*

Step 7 Create the rs Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.CREATE_RULE_SET(
        rule_set_name      => 'rs',
        evaluation_context => 'evalctx',
        rule_set_comment   => 'support rules');
    END;
    /

    /*

Step 8 Add the Rules to the Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r1',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r2',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r3',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r4',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r5',
        rule_set_name => 'rs');
    END;
    /

    /*

Step 9 Create the problem_dispatch PL/SQL Procedure

    */

    CREATE OR REPLACE PROCEDURE problem_dispatch
    IS
        cursor c  is SELECT probid, rowid FROM PROBLEMS WHERE center IS NULL;
        tv        SYS.RE$TABLE_VALUE;
        tvl       SYS.RE$TABLE_VALUE_LIST;
        vv1       SYS.RE$VARIABLE_VALUE;
        vvl       SYS.RE$VARIABLE_VALUE_LIST;
        truehits  SYS.RE$RULE_HIT_LIST;
        maybehits SYS.RE$RULE_HIT_LIST;
        ac        SYS.RE$NV_LIST;
        namearray SYS.RE$NAME_ARRAY;
        name      VARCHAR2(30);
        cval      VARCHAR2(100);
        rnum      INTEGER;
        i         INTEGER;
        status    PLS_INTEGER;
    BEGIN
      FOR r IN c LOOP
        tv  := sYS.RE$TABLE_VALUE('prob', ROWIDTOCHAR(r.rowid));
        tvl := SYS.RE$TABLE_VALUE_LIST(tv);
        vv1 := SYS.RE$VARIABLE_VALUE('current_time',
                                     ANYDATA.CONVERTDATE(SYSDATE));
        vvl := SYS.RE$VARIABLE_VALUE_LIST(vv1);
        truehits := SYS.RE$RULE_HIT_LIST();
        maybehits := SYS.RE$RULE_HIT_LIST();
        DBMS_RULE.EVALUATE(
            rule_set_name        => 'support.rs',
            evaluation_context   => 'evalctx',
            table_values         => tvl,
            variable_values      => vvl,
            true_rules           => truehits,
            maybe_rules          => maybehits);
        FOR rnum IN 1..truehits.COUNT loop
          DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
          ac := truehits(rnum).rule_action_context;
          namearray := ac.GET_ALL_NAMES;
          FOR i in 1..namearray.COUNT LOOP
            name := namearray(i);
            status := ac.GET_VALUE(name).GETVARCHAR2(cval);
            IF (name = 'CENTER') THEN
              UPDATE problems SET center = cval
              WHERE rowid = r.rowid;
              DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval);
            ELSIF (name = 'ALERT') THEN
              DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid);
            END IF;
          END LOOP;
        END LOOP;
      END LOOP;
    END;
    /

    /*

Step 10 Log Problems

    */

    INSERT INTO problems(probid, custid, priority, description)
      VALUES(10201, 12, 1, 'no dial tone');

    INSERT INTO problems(probid, custid, priority, description)
      VALUES(10202, 22, 2, 'noise on local calls');

    INSERT INTO PROBLEMS(probid, custid, priority, description)
      VALUES(10203, 32, 3, 'noise on long distance calls');

    COMMIT;

    /*

Step 11 Check the Spool Results

Check the `rules_var_tab.out` spool file to ensure that all actions completed successfully after this script completes.

    */

    SET ECHO OFF
    SPOOL OFF

    /*************************** END OF SCRIPT ******************************/

See Also: "Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem dispatch

## Using Rules on Implicit Variables and Table Data

This example illustrates how to use rules to evaluate implicit variables and data stored in a table. The application uses the `problems` table in the `support` schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:

- Assign all problems with priority greater than 2 to the San Jose Center.

- Assign all problems with priority equal to 2 to the New York Center.

- Assign all problems with priority equal to 1 to the Tampa Center from 8 AM to 8 PM.

- Assign all problems with priority equal to 1 to the Bangalore Center after 8 PM and before 8 AM.

- Send an alert to the vice president of support for a problem with priority equal to 1.

The evaluation context consists of the `problems` table. The relevant row of the table, which corresponds to the problem being routed, is passed to the `DBMS_RULE.EVALUATE` procedure as a table value.

As in the example illustrated in "Using Rules on Both Explicit Variables and Table Data", the current time is represented as a variable named `current_time`. However, this variable value is not specified during evaluation by the caller. That is, `current_time` is an implicit variable in this example. A PL/SQL function named `timefunc` is specified for `current_time`, and this function is invoked once during evaluation to get its value.

Using implicit variables can be useful in other cases if one of the following conditions is true:

- The caller does not have access to the variable value.

- The variable is referenced infrequently in rules. Because it is implicit, its value can be retrieved only when necessary, and does not need to be passed in for every evaluation.

Complete the following steps:

1. Show Output and Spool Results

2. Create the support User

3. Grant the support User the Necessary System Privileges on Rules

4. Create the problems Table

5. Create the timefunc Function to Return the Value of current_time

6. Create the evalctx Evaluation Context

7. Create the Rules that Correspond to Problem Priority

8. Create the rs Rule Set

9. Add the Rules to the Rule Set

10. Create the problem_dispatch PL/SQL Procedure

11. Log Problems

12. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

    /************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run `SET` `ECHO` `ON` and specify the spool file for the script. Check the spool file for errors after you run this script.

    */

    SET ECHO ON
    SPOOL rules_implicit_var.out

    /*

Step 2 Create the support User

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    CREATE TABLESPACE support_tbs3 DATAFILE 'support_tbs3.dbf'   SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    CREATE USER support
    IDENTIFIED BY support
      DEFAULT TABLESPACE support_tbs3
      QUOTA UNLIMITED ON support_tbs3;

    GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
      CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE,
      CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE
    TO support;

    /*

Step 3 Grant the support User the Necessary System Privileges on Rules

    */

    BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
        grantee      => 'support',
        grant_option => false);
    END;
    /

    /*

Step 4 Create the problems Table

    */

    CONNECT support/support

    SET FEEDBACK 1
    SET NUMWIDTH 10
    SET LINESIZE 80
    SET TRIMSPOOL ON
    SET TAB OFF
    SET PAGESIZE 100
    SET SERVEROUTPUT ON

    CREATE TABLE problems(
      probid          NUMBER PRIMARY KEY,
      custid          NUMBER,
      priority        NUMBER,
      description     VARCHAR2(4000),
      center          VARCHAR2(100));

    /*

Step 5 Create the timefunc Function to Return the Value of current_time

    */

    CREATE OR REPLACE FUNCTION timefunc(
      eco    VARCHAR2,
      ecn    VARCHAR2,
      var    VARCHAR2,
      evctx  SYS.RE$NV_LIST)
    RETURN SYS.RE$VARIABLE_VALUE
    IS
    BEGIN
      IF (var = 'CURRENT_TIME') THEN
        RETURN(SYS.RE$VARIABLE_VALUE('current_time',
                                     ANYDATA.CONVERTDATE(SYSDATE)));
      ELSE
        RETURN(NULL);
      END IF;
    END;
    /

    /*

Step 6 Create the evalctx Evaluation Context

    */

    DECLARE
      ta SYS.RE$TABLE_ALIAS_LIST;
      vt SYS.RE$VARIABLE_TYPE_LIST;
    BEGIN
      ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems'));
      vt := SYS.RE$VARIABLE_TYPE_LIST(
              SYS.RE$VARIABLE_TYPE('current_time', 'DATE', 'timefunc', NULL));
      DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
        evaluation_context_name    => 'evalctx',
        table_aliases              => ta,
        variable_types             => vt,
        evaluation_context_comment => 'support problem definition');
    END;
    /

    /*

Step 7 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

    */

    DECLARE
      ac SYS.RE$NV_LIST;
    BEGIN
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('San Jose'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r1',
        condition      => 'prob.priority > 2',
        action_context => ac,
        rule_comment   => 'Low priority problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('New York'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r2',
        condition      => 'prob.priority = 2',
        action_context => ac,
        rule_comment   => 'High priority problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('ALERT', ANYDATA.CONVERTVARCHAR2('John Doe'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name      => 'r3',
        condition      => 'prob.priority = 1',
        action_context => ac,
        rule_comment   => 'Urgent problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('Tampa'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name => 'r4',
        condition => '(prob.priority = 1) and ' ||
                     '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) >= 8) and ' ||
                     '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) <= 20)',
        action_context => ac,
        rule_comment   => 'Urgent daytime problems');
      ac := SYS.RE$NV_LIST(NULL);
      ac.add_pair('CENTER', ANYDATA.CONVERTVARCHAR2('Bangalore'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name => 'r5',
        condition => '(prob.priority = 1) and ' ||
                     '((TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) < 8) or ' ||
                     ' (TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) > 20))',
        action_context => ac,
        rule_comment => 'Urgent nighttime problems');
    END;
    /

    /*

Step 8 Create the rs Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.CREATE_RULE_SET(
        rule_set_name      => 'rs',
        evaluation_context => 'evalctx',
        rule_set_comment   => 'support rules');
    END;
    /

    /*

Step 9 Add the Rules to the Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r1',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r2',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r3',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r4',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r5',
        rule_set_name => 'rs');
    END;
    /

    /*

Step 10 Create the problem_dispatch PL/SQL Procedure

    */

    CREATE OR REPLACE PROCEDURE problem_dispatch
    IS
        cursor c  IS SELECT probid, rowid FROM problems WHERE center IS NULL;
        tv        SYS.RE$TABLE_VALUE;
        tvl       SYS.RE$TABLE_VALUE_LIST;
        truehits  SYS.RE$RULE_HIT_LIST;
        maybehits SYS.RE$RULE_HIT_LIST;
        ac        SYS.RE$NV_LIST;
        namearray SYS.RE$NAME_ARRAY;
        name      VARCHAR2(30);
        cval      VARCHAR2(100);
        rnum      INTEGER;
        i         INTEGER;
        status    PLS_INTEGER;
    BEGIN
      FOR r IN c LOOP
        tv  := SYS.RE$TABLE_VALUE('prob', rowidtochar(r.rowid));
        tvl := SYS.RE$TABLE_VALUE_LIST(tv);
        truehits := SYS.RE$RULE_HIT_LIST();
        maybehits := SYS.RE$RULE_HIT_LIST();
        DBMS_RULE.EVALUATE(
            rule_set_name        => 'support.rs',
            evaluation_context   => 'evalctx',
            table_values         => tvl,
            true_rules           => truehits,
            maybe_rules          => maybehits);
        FOR rnum IN 1..truehits.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
          ac := truehits(rnum).rule_action_context;
          namearray := ac.GET_ALL_NAMES;
          FOR i IN 1..namearray.COUNT LOOP
            name := namearray(i);
            status := ac.GET_VALUE(name).GETVARCHAR2(cval);
            IF (name = 'CENTER') THEN
              UPDATE problems SET center = cval
                WHERE rowid = r.rowid;
              DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval);
            ELSIF (name = 'ALERT') THEN
              DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid);
            END IF;
          END LOOP;
        END LOOP;
      END LOOP;
    END;
    /

    /*

Step 11 Log Problems

    */

    INSERT INTO problems(probid, custid, priority, description)
      VALUES(10301, 13, 1, 'no dial tone');

    INSERT INTO problems(probid, custid, priority, description)
      VALUES(10302, 23, 2, 'noise on local calls');

    INSERT INTO problems(probid, custid, priority, description)
      VALUES(10303, 33, 3, 'noise on long distance calls');

    COMMIT;

    /*

Step 12 Check the Spool Results

Check the `rules_implicit_var.out` spool file to ensure that all actions completed successfully after this script completes.

    */

    SET ECHO OFF
    SPOOL OFF

    /*************************** END OF SCRIPT ******************************/

See Also: "Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem dispatch

## Using Event Contexts and Implicit Variables with Rules

An event context is a varray of type `SYS.RE$NV_LIST` that contains name-value pairs that contain information about the event. This optional information is not directly used or interpreted by the rules engine. Instead, it is passed to client callbacks such as an evaluation function, a variable value function (for implicit variables), or a variable method function.

In this example, assume every customer has a primary contact person, and the goal is to assign the problem reported by a customer to the support center to which the customer's primary contact person belongs. The customer name is passed in the event context.

This example illustrates how to use event contexts with rules to evaluate implicit variables. Specifically, when an event is evaluated using the `DBMS_RULE.EVALUATE` procedure, the event context is passed to the variable value function for implicit variables in the evaluation context. The name of the variable value function is `find_contact`, and this PL/SQL function returns the contact person based on the name of the company specified in the event context. The rule set is evaluated based on the contact person name and the priority for an event.

This example uses the following rules for handling customer problems:

- Assign all problems that belong to Jane to the San Jose Center.

- Assign all problems that belong to Fred to the New York Center.

- Assign all problems whose primary contact is unknown to George at the Texas Center.

- Send an alert to the vice president of support for a problem with priority equal to 1.

Complete the following steps:

1. Show Output and Spool Results

2. Create the support User

3. Grant the support User the Necessary System Privileges on Rules

4. Create the find_contact Function to Return a Customer's Contact

5. Create the evalctx Evaluation Context

6. Create the Rules that Correspond to Problem Priority and Contact

7. Create the rs Rule Set

8. Add the Rules to the Rule Set

9. Query the Data Dictionary

10. Create the problem_dispatch PL/SQL Procedure

11. Dispatch Sample Problems

12. Clean Up the Environment (Optional)

13. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

    /************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run `SET` `ECHO` `ON` and specify the spool file for the script. Check the spool file for errors after you run this script.

    */

    SET ECHO ON
    SPOOL rules_event_context.out

    /*

Step 2 Create the support User

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
      CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE,
      CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE
    TO support IDENTIFIED BY support;

    /*

Step 3 Grant the support User the Necessary System Privileges on Rules

    */

    BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
        grantee      => 'support',
        grant_option => false);
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
        grantee      => 'support',
        grant_option => false);
    END;
    /

    /*

Step 4 Create the find_contact Function to Return a Customer's Contact

    */

    CONNECT support/support

    SET FEEDBACK 1
    SET NUMWIDTH 10
    SET LINESIZE 80
    SET TRIMSPOOL ON
    SET TAB OFF
    SET PAGESIZE 100
    SET SERVEROUTPUT ON
    CREATE OR REPLACE FUNCTION find_contact(
      eco       VARCHAR2,
      ecn       VARCHAR2,
      var       VARCHAR2,
      evctx     SYS.RE$NV_LIST)
    RETURN SYS.RE$VARIABLE_VALUE IS
      cust      VARCHAR2(30);
      contact   VARCHAR2(30);
      status    PLS_INTEGER;
    BEGIN
      IF (var = 'CUSTOMER_CONTACT') THEN
        status := evctx.GET_VALUE('CUSTOMER').GETVARCHAR2(cust);
        IF (cust = 'COMPANY1') THEN     -- COMPANY1's contact person is Jane
          contact := 'JANE';
        ELSIF (cust = 'COMPANY2') THEN  -- COMPANY2's contact person is Fred
          contact := 'FRED';
        ELSE        -- Assign customers without primary contact person to George
          contact := 'GEORGE';
        END IF;
        RETURN SYS.RE$VARIABLE_VALUE('customer_contact',
                                     ANYDATA.CONVERTVARCHAR2(contact));
      ELSE
        RETURN NULL;
      END IF;
    END;
    /

    /*

Step 5 Create the evalctx Evaluation Context

    */

    DECLARE
      vt  SYS.RE$VARIABLE_TYPE_LIST;
    BEGIN
      vt := SYS.RE$VARIABLE_TYPE_LIST(
            SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL),
            SYS.RE$VARIABLE_TYPE('customer_contact', 'VARCHAR2(30)',
                                 'find_contact', NULL));
      DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
        evaluation_context_name    => 'evalctx',
        variable_types             => vt,
        evaluation_context_comment => 'support problem definition');
    END;
    /

    /*

Step 6 Create the Rules that Correspond to Problem Priority and Contact

The following code creates one action context for each rule, and one name-value pair in each action context.

    */

    DECLARE
      ac  SYS.RE$NV_LIST;
    BEGIN
      ac := SYS.RE$NV_LIST(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('San Jose'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name       => 'r1',
        condition       => ':customer_contact = ''JANE''',
        action_context  => ac,
        rule_comment    => 'Jane''s customer problems');
      ac := sys.re$nv_list(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('New York'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name       => 'r2',
        condition       => ':customer_contact = ''FRED''',
        action_context  => ac,
        rule_comment    => 'Fred''s customer problems');
      ac := sys.re$nv_list(NULL);
      ac.ADD_PAIR('CENTER', ANYDATA.CONVERTVARCHAR2('Texas'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name       => 'r3',
        condition       => ':customer_contact = ''GEORGE''',
        action_context  => ac,
        rule_comment    => 'George''s customer problems');
      ac := sys.re$nv_list(NULL);
      ac.ADD_PAIR('ALERT', ANYDATA.CONVERTVARCHAR2('John Doe'));
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name       => 'r4',
        condition       => ':priority=1',
        action_context  => ac,
        rule_comment    => 'Urgent problems');
    END;
    /

    /*

Step 7 Create the rs Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.CREATE_RULE_SET(
        rule_set_name      => 'rs',
        evaluation_context => 'evalctx',
        rule_set_comment   => 'support rules');
    END;
    /

    /*

Step 8 Add the Rules to the Rule Set

    */

    BEGIN
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r1',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r2',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r3',
        rule_set_name => 'rs');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name     => 'r4',
        rule_set_name => 'rs');
    END;
    /

    /*

Step 9 Query the Data Dictionary

At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.

    */

    COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
    COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40

    SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT
      FROM USER_EVALUATION_CONTEXTS
      ORDER BY EVALUATION_CONTEXT_NAME;

    SET LONGCHUNKSIZE 4000
    SET LONG 4000
    COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5
    COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35
    COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10
    COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10

    SELECT RULE_NAME,
           RULE_CONDITION,
           AC.NVN_NAME ACTION_CONTEXT_NAME,
           AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
      FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
      ORDER BY RULE_NAME;

    COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
    COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
    COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25
    COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15

    SELECT RULE_SET_NAME,
           RULE_SET_EVAL_CONTEXT_OWNER,
           RULE_SET_EVAL_CONTEXT_NAME,
           RULE_SET_COMMENT
      FROM USER_RULE_SETS
      ORDER BY RULE_SET_NAME;

    /*

Step 10 Create the problem_dispatch PL/SQL Procedure

    */

    CREATE OR REPLACE PROCEDURE problem_dispatch (priority  NUMBER,
                                                  customer  VARCHAR2)
    IS
        vvl       SYS.RE$VARIABLE_VALUE_LIST;
        truehits  SYS.RE$RULE_HIT_LIST;
        maybehits SYS.RE$RULE_HIT_LIST;
        ac        SYS.RE$NV_LIST;
        namearray SYS.RE$NAME_ARRAY;
        name      VARCHAR2(30);
        cval      VARCHAR2(100);
        rnum      INTEGER;
        i         INTEGER;
        status    PLS_INTEGER;
        evctx     SYS.RE$NV_LIST;
    BEGIN
      vvl  := SYS.RE$VARIABLE_VALUE_LIST(
                SYS.RE$VARIABLE_VALUE('priority',
                                      ANYDATA.CONVERTNUMBER(priority)));
      evctx := SYS.RE$NV_LIST(NULL);
      evctx.ADD_PAIR('CUSTOMER', ANYDATA.CONVERTVARCHAR2(customer));
      truehits  := SYS.RE$RULE_HIT_LIST();
      maybehits := SYS.RE$RULE_HIT_LIST();
      DBMS_RULE.EVALUATE(
          rule_set_name        => 'support.rs',
          evaluation_context   => 'evalctx',
          event_context        => evctx,
          variable_values      => vvl,
          true_rules           => truehits,
          maybe_rules          => maybehits);
      FOR rnum IN 1..truehits.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
        ac := truehits(rnum).rule_action_context;
        namearray := ac.GET_ALL_NAMES;
          FOR i IN 1..namearray.count LOOP
            name := namearray(i);
            status := ac.GET_VALUE(name).GETVARCHAR2(cval);
            IF (name = 'CENTER') THEN
              DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
            ELSIF (name = 'ALERT') THEN
              DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval);
            END IF;
          END LOOP;
      END LOOP;
    END;
    /

    /*

Step 11 Dispatch Sample Problems

The first problem dispatch in this step uses the event context and the variable value function to determine the contact person for `COMPANY1`. The event context is passed to the `find_contact` variable value function, and this function returns the contact name `JANE`. Therefore, rule `r1` evaluates to `TRUE`. The `problem_dispatch` procedure sends the problem to the San Jose office because `JANE` belongs to that office. In addition, the priority for this event is 1, which causes rule `r4` to evaluate to `TRUE`. As a result, the `problem_dispatch` procedure sends an alert to John Doe.

The second problem dispatch in this step uses the event context and the variable value function to determine the contact person for `COMPANY2`. The event context is passed to the `find_contact` variable value function, and this function returns the contact name `FRED`. Therefore, rule `r2` evaluates to `TRUE`. The `problem_dispatch` procedure sends the problem to the New York office because `FRED` belongs to that office.

The third problem dispatch in this step uses the event context and the variable value function to determine the contact person for `COMPANY3`. This company does not have a dedicated contact person. The event context is passed to the `find_contact` variable value function, and this function returns the contact name `GEORGE`, because `GEORGE` is the default contact when no contact person is found. Therefore, rule `r3` evaluates to `TRUE`. The `problem_dispatch` procedure sends the problem to the Texas office because `GEORGE` belongs to that office.

    */

    EXECUTE problem_dispatch(1, 'COMPANY1');
    EXECUTE problem_dispatch(2, 'COMPANY2');
    EXECUTE problem_dispatch(5, 'COMPANY3');

    /*

Step 12 Clean Up the Environment (Optional)

You can clean up the sample environment by dropping the `support` user.

    */

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    DROP USER support CASCADE;

    /*

Step 13 Check the Spool Results

Check the `rules_event_context.out` spool file to ensure that all actions completed successfully after this script completes.

    */

    SET ECHO OFF
    SPOOL OFF

    /*************************** END OF SCRIPT ******************************/

## Dispatching Problems and Checking Results for the Table Examples

The following sections configure a `problem_dispatch` procedure that updates information in the `problems` table:

- "Using Rules on Data Stored in a Table"

- "Using Rules on Both Explicit Variables and Table Data"

- "Using Rules on Implicit Variables and Table Data"

The steps in this section dispatch the problems by running the `problem_dispatch` procedure and display the results in the `problems` table.

Step 1 Query the Data Dictionary

View the evaluation context, rules, and rule set you created in the example:

    CONNECT support/support

    COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
    COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40

    SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT
      FROM USER_EVALUATION_CONTEXTS
      ORDER BY EVALUATION_CONTEXT_NAME;

    SET LONGCHUNKSIZE 4000
    SET LONG 4000
    COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5
    COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35
    COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10
    COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10

    SELECT RULE_NAME,
           RULE_CONDITION,
           AC.NVN_NAME ACTION_CONTEXT_NAME,
           AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
      FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
      ORDER BY RULE_NAME;

    COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
    COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
    COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25
    COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15

    SELECT RULE_SET_NAME,
           RULE_SET_EVAL_CONTEXT_OWNER,
           RULE_SET_EVAL_CONTEXT_NAME,
           RULE_SET_COMMENT
      FROM USER_RULE_SETS
      ORDER BY RULE_SET_NAME;

Step 2 List the Problems in the problems Table

This `SELECT` statement should show the problems logged previously.

    COLUMN probid HEADING 'Problem ID' FORMAT 99999
    COLUMN custid HEADING 'Customer ID' FORMAT 99
    COLUMN priority HEADING 'Priority' FORMAT 9
    COLUMN description HEADING 'Problem Description' FORMAT A30
    COLUMN center HEADING 'Center' FORMAT A10

    SELECT probid, custid, priority, description, center FROM problems
      ORDER BY probid;

Your output looks similar to the following:

    Problem ID Customer ID Priority Problem Description            Center
    ---------- ----------- -------- ------------------------------ ----------
         10301          13        1 no dial tone
         10302          23        2 noise on local calls
         10303          33        3 noise on long distance calls

Notice that the `Center` column is `NULL` for each new row inserted.

Step 3 Dispatch the Problems by Running the problem_dispatch Procedure

Execute the `problem_dispatch` procedure.

    SET SERVEROUTPUT ON
    EXECUTE problem_dispatch;

Step 4 List the Problems in the problems Table

If the problems were dispatched successfully in Step 3, then this `SELECT` statement should show the center to which each problem was dispatched in the `Center` column.

    SELECT probid, custid, priority, description, center FROM problems
      ORDER BY probid;

Your output looks similar to the following:

    Problem ID Customer ID Priority Problem Description            Center
    ---------- ----------- -------- ------------------------------ ----------
         10201          12        1 no dial tone                   Tampa
         10202          22        2 noise on local calls           New York
         10203          32        3 noise on long distance calls   San Jose

Note: The output will vary depending on which example you used to create the `problem_dispatch` procedure.

Step 5 Clean Up the Environment (Optional)

You can clean up the sample environment by dropping the `support` user.

    CONNECT SYSTEM/MANAGER AS SYSDBA;

    DROP USER support CASCADE;